#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   c2b_config_utils.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2022-10-19 11:34   pengwei.sun      1.0         None
'''

from src.utils.db_processor import presto_processor

andoriod_product_ids =(29186,17881,43514,15144,17751,38541,7424,34572,15145,25399,27969,28925,25471,66429,17872,35819,28924,108423,7425,
                       38508,9021,26470,24505,8218,9641,43598,29185,17853,34571,108420,9640,7427,35818,8757,108422,17882,32411
    )

query_process_sql = """
select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
from
(
select product_id,product_name,secondary_level_id,secondary_level_name,memory,storage,
purchase_way ,guarantee ,networks,
avg(item_quotation_price_num) as item_quotation_price_num_avg,
min(item_quotation_price_num) as item_quotation_price_num_min,
max(item_quotation_price_num) as item_quotation_price_num_max,
approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_capacity_name as memory ,
dpk.memory_capacity_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,secondary_level_id,secondary_level_name,memory,storage,
purchase_way ,guarantee ,networks
)a
left join  (SELECT dp.product_id,dpk.memory_capacity_name as storage,approx_percentile(psdp.settle_amount_num,0.5) item_quotation_price_num_media,
count(1) as cnt
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
group by 1,2) b
on a.product_id=b.product_id and a.storage=b.storage

"""
# where a.product_id in (25827)
#获取型号 等级，ppv组合的售卖量信息
query_ppv_combine_sql="""

select 
product_id,product_name,secondary_level_id,
storage,purchase_way,guarantee,networks,count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_capacity_name as memory ,
dpk.memory_capacity_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
  )a
group by 1,2,3,4,5,6,7

"""
#获取ppv打分和rank数据
query_ppv_rank_sql = """
  select distinct column_name,column_value_name,ppv_rank, ppv_rank as low_ppv_rank from algo.algo_c2b_tablet_ppv_rank
 """

#获取型号维度的ppv组合的出货数量
query_product_ppv_cnt="""
select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
from
(
select product_id,product_name,storage,
purchase_way ,guarantee ,networks,
avg(item_quotation_price_num) as item_quotation_price_num_avg,
min(item_quotation_price_num) as item_quotation_price_num_min,
max(item_quotation_price_num) as item_quotation_price_num_max,
approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_capacity_name as memory ,
dpk.memory_capacity_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}

)a
group by product_id,product_name,storage,
purchase_way ,guarantee ,networks
)a
left join (SELECT dp.product_id,dpk.memory_capacity_name as storage,approx_percentile(psdp.settle_amount_num,0.5) item_quotation_price_num_media,
count(1) as cnt
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
group by 1,2) b
on a.product_id=b.product_id and a.storage=b.storage

"""
# and dpk.purchase_channel_name ='大陆国行（苹果系统型号M开头）'

#sku+ppv组合的售卖量
query_sku_sale_cnt="""
select product_id,product_name,product_sku_id,storage,
purchase_way ,guarantee ,networks,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
  dpk.product_sku_id,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
  substring(level.product_level_name,1,1) as level_sub,
cdp.product_no, 
dpk.memory_capacity_name as memory ,
dpk.memory_capacity_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,product_sku_id,storage,
purchase_way ,guarantee ,networks
"""

query_level_sub_ppv_sql="""
select product_id,product_name,level_sub,storage,
purchase_way ,guarantee ,networks,
avg(item_quotation_price_num) as level_item_quotation_avg_price ,
count(1) as level_cnt_product
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
  substring(level.product_level_name,1,1) as level_sub,
cdp.product_no, 
dpk.memory_capacity_name as memory ,
dpk.memory_capacity_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,level_sub,storage,
purchase_way ,guarantee ,networks

"""

query_ppv_combine_all="""
select distinct dpk.product_id,dpk.product_name,
dpk.memory_capacity_name as storage , 
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks
FROM  dim.dim_product_sku dpk 
JOIN dim.dim_product_sku_channel_mapping dps 
ON dpk.product_sku_id = dps.mapping_product_sku_id 
where dpk.product_category_id=6 and dpk.product_brand_id =52  and dpk.is_product_sku_active_flag=1 and dpk.business_channel='0'
      and dpk.if_c2b_product_flag=1
      and dpk.product_id in {}
order by 1 desc,dpk.memory_capacity_name,dpk.purchase_channel_name,dpk.network_standard_name
""".format(andoriod_product_ids)


query_product_ppv_small_version_cnt="""
select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
from
(
select product_id,product_name,storage,
purchase_way ,guarantee ,networks,small_version,
avg(item_quotation_price_num) as item_quotation_price_num_avg,
min(item_quotation_price_num) as item_quotation_price_num_min,
max(item_quotation_price_num) as item_quotation_price_num_max,
approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_capacity_name as memory ,
dpk.memory_capacity_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.warranty_duration_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,storage,
purchase_way ,guarantee ,networks,small_version
)a
left join  (SELECT dp.product_id,dpk.memory_capacity_name as storage,approx_percentile(psdp.settle_amount_num,0.5) item_quotation_price_num_media,
count(1) as cnt
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_tablet cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 6 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id =52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
group by 1,2) b
on a.product_id=b.product_id and a.storage=b.storage

"""

BASE_LEVEL_RANK="""
SELECT 
product_level_id as secondary_level_id,product_level_name as secondary_level_name
FROM dim.dim_product_level a
WHERE product_category_id = 6 AND is_product_level_active_flag = 1
"""

ppv_rank_df = presto_processor.load_sql(query_ppv_rank_sql)

def get_level_rank():
    level_rank_df = presto_processor.load_sql(BASE_LEVEL_RANK)
    return level_rank_df

#获取型号 等级，ppv组合的售卖量信息
def get_ppv_combine_sql(start1_date,start2_date,end_date):
    ppv_level_df = presto_processor.load_sql(query_ppv_combine_sql.format(start1_date,start2_date,end_date,andoriod_product_ids))
    ppv_level_df['storage'] = ppv_level_df['storage'].fillna('unknown')
    ppv_level_df['purchase_way'] = ppv_level_df['purchase_way'].fillna('unknown')
    ppv_level_df['guarantee'] = ppv_level_df['guarantee'].fillna('unknown')
    ppv_level_df['networks'] = ppv_level_df['networks'].fillna('unknown')
    ppv_level_df['ppv_combine'] = ppv_level_df['storage'] + '_' + ppv_level_df['purchase_way'] + '_' + ppv_level_df['guarantee'] + '_' + ppv_level_df['networks']

    ppv_cnt_df = ppv_level_df.groupby(by=['product_id','secondary_level_id','storage'])['cnt'].agg({'total_cnt': 'sum'}).reset_index()
    ppv_level_df = ppv_level_df.merge(ppv_cnt_df,on=['product_id','secondary_level_id','storage'])
    ppv_level_df['percent']=ppv_level_df['cnt']/ppv_level_df['total_cnt'] #型号的ppv组合占总售卖量的比率
    ppv_level_df['percent_rank'] = ppv_level_df[['product_id','secondary_level_id','storage','percent']].groupby(by=['product_id','secondary_level_id','storage'])['percent'].rank(ascending=False, method='first')

    return ppv_level_df

#获取所有ppv组合的及其各个组合的得分
def get_all_combine_data_rank_score():
    combine_df = presto_processor.load_sql(query_ppv_combine_all)
    combine_df['storage'] = combine_df['storage'].fillna('unknown')
    combine_df['purchase_way'] = combine_df['purchase_way'].fillna('unknown')
    combine_df['guarantee'] = combine_df['guarantee'].fillna('unknown')
    combine_df['networks'] = combine_df['networks'].fillna('unknown')
    # ppv_rank_df = presto_processor.load_sql(query_ppv_rank_sql)

    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'storage', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='storage', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "storage_rank","low_ppv_rank": "storage_low_rank"})
    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'purchase_way', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='purchase_way', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "purchase_way_rank","low_ppv_rank": "purchase_way_low_rank"})
    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'guarantee', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='guarantee', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "guarantee_rank","low_ppv_rank": "guarantee_low_rank"})
    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'networks', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='networks', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "networks_rank","low_ppv_rank": "networks_low_rank"})

    combine_df['storage_rank']=combine_df['storage_rank'].fillna(100)
    combine_df['storage_low_rank']=combine_df['storage_low_rank'].fillna(100)
    combine_df['purchase_way_rank']=combine_df['purchase_way_rank'].fillna(100)
    combine_df['purchase_way_low_rank']=combine_df['purchase_way_low_rank'].fillna(100)
    combine_df['guarantee_rank']=combine_df['guarantee_rank'].fillna(100)
    combine_df['guarantee_low_rank']=combine_df['guarantee_low_rank'].fillna(100)
    combine_df['networks_rank']=combine_df['networks_rank'].fillna(100)
    combine_df['networks_low_rank']=combine_df['networks_low_rank'].fillna(100)
    # combine_df['small_version_rank']=combine_df['small_version_rank'].fillna(100)
    # combine_df['small_version_rank_rate'] = combine_df['small_version_rank']*1.00000/100
    combine_df['rank_score'] = combine_df['storage_rank'] * combine_df['purchase_way_rank'] * combine_df['guarantee_rank'] * combine_df['guarantee_rank'] * combine_df['networks_rank']
    combine_df['rank_low_score'] = combine_df['storage_low_rank'] * combine_df['purchase_way_low_rank'] * combine_df['guarantee_low_rank'] * combine_df['networks_low_rank']
    combine_df.loc[combine_df.product_id.isin([27640,27639,27637,25827,25680,25679,23423,23422,20079,17752,17726,17462,17461]), 'rank_score'] = combine_df.loc[combine_df.product_id.isin([27640,27639,27637,25827,25680,25679,23423,23422,20079,17752,17726,17462,17461]), 'rank_low_score']

    combine_df['ppv_combine'] = combine_df['storage'] + '_' + combine_df['purchase_way'] + '_' + combine_df['guarantee'] + '_' + combine_df['networks']

    return  combine_df

# 获取ppv的rank score数据
def get_product_base_ppv_fun(combine_df,start1_date,start2_date,end_date):
    df = presto_processor.load_sql(query_product_ppv_cnt.format(start1_date,start2_date,end_date,andoriod_product_ids,start1_date,start2_date,end_date,andoriod_product_ids))
    #聚合ppv
    df['storage']=df['storage'].fillna('unknown')
    df['purchase_way']=df['purchase_way'].fillna('unknown')
    df['guarantee']=df['guarantee'].fillna('unknown')
    df['networks']=df['networks'].fillna('unknown')

    df['ppv_combine'] = df['storage'] + '_' + df['purchase_way'] + '_' + df['guarantee'] + '_' + df['networks']
    df['ppv_base_rank'] = df[['product_id','storage','purchase_way','guarantee','networks','ppv_combine','cnt']].groupby(by=['product_id','storage'])['cnt'].rank(ascending=False, method='first')

    # 获取ppv的rank score数据
    df = df.merge(combine_df[['product_id','ppv_combine','rank_score','rank_low_score']],on=['product_id','ppv_combine'])
    df =df.rename(columns={"rank_score": "rank_score_base","rank_low_score": "rank_low_score_base"})

    ret_df = df.loc[df.ppv_base_rank==1,['product_id','ppv_combine','storage','cnt','ppv_base_rank','rank_score_base','rank_low_score_base']] #确保有竟得的组合中留下那些出货量最多的组合
    return ret_df

def query_level_sub_ppv_price(start1_date,start2_date,end_date):
    df = presto_processor.load_sql(query_level_sub_ppv_sql.format(start1_date,start2_date,end_date,andoriod_product_ids))
    df['storage']=df['storage'].fillna('unknown')
    df['purchase_way']=df['purchase_way'].fillna('unknown')
    df['guarantee']=df['guarantee'].fillna('unknown')
    df['networks']=df['networks'].fillna('unknown')

    df['ppv_combine'] = df['storage'] + '_' + df['purchase_way'] + '_' + df['guarantee'] + '_' + df['networks']
    return df